import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'dart:io';

// 导入表的定义文件
part 'user_db.g.dart';

// 定义表结构
class Users extends Table {
  IntColumn get id => integer().autoIncrement()(); // 主键，自增
  TextColumn get name => text().withLength(min: 1, max: 50)(); // 用户名，限制长度
  IntColumn get age => integer().nullable()(); // 年龄，可以为 null
}

// 定义数据库，使用@DriftDatabase注解来引用表
@DriftDatabase(tables: [Users])
class AppDatabase extends _$AppDatabase {
  AppDatabase._internal() : super(_openConnection()); // 私有的构造函数
  static final AppDatabase instance = AppDatabase._internal(); // 单例
  // 定义数据库版本
  @override
  int get schemaVersion => 1;

  // 增、删、改、查方法
  Future<int> insertUser(UsersCompanion user) => into(users).insert(user);
  Future<List<User>> getAllUsers() => select(users).get();
  Future<int> deleteUser(int id) =>
      (delete(users)..where((tbl) => tbl.id.equals(id))).go();
  // 查询年龄为25岁的用户
  Future<List<User>> getUsersWithAge({required int age}) {
    return (select(users)..where((tbl) => tbl.age.equals(age))).get();
  }

  //查询有多少条数据
  Future<int?> getUserCount() {
    final query = selectOnly(users)..addColumns([users.id.count()]);
    return query.map((row) => row.read(users.id.count())).getSingle();
  }
}

// 数据库连接配置
LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase(file);
  });
}

void printAllUsers(AppDatabase db) async {
  try {
    final allUsers = await db.getAllUsers(); // 获取所有用户
    if (allUsers.isNotEmpty) {
      for (var user in allUsers) {
        print(
            'User ID: ${user.id}, Name: ${user.name}, Age: ${user.age}'); // 打印每个用户的信息
      }
    } else {
      print('No users found in the database.');
    }
  } catch (e) {
    print('Failed to fetch users: $e');
  }
}
